package cz.acies.uniql.sql;

import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;


import cz.acies.uniql.dialect.Dialect;
import cz.acies.uniql.metadata.DBMetaData;
import cz.acies.uniql.metadata.FieldMetaData;
import cz.acies.uniql.metadata.TableMetaData;
import cz.acies.uniql.sql.UniQLExpr;
import cz.acies.uniql.sql.element.UniQLAlias;
import cz.acies.uniql.sql.element.UniQLColumn;
import cz.acies.uniql.sql.element.UniQLCondition;
import cz.acies.uniql.sql.element.UniQLCriterion;
import cz.acies.uniql.sql.element.UniQLFrom;
import cz.acies.uniql.sql.element.UniQLJoin;
import cz.acies.uniql.sql.element.UniQLWhere;

public class UpdateQuery extends Query
{
	private static final long serialVersionUID = -932441780336784509L;
	
	private ArrayList<UniQLExpr> tables;
	private ArrayList<UniQLExpr> joins;
	private ArrayList<HashMap<String, Object>> parameters;
	private UniQLWhere where;

	
	/**
	 * Konstruktor tridy UpdateQuery.
	 */
	public UpdateQuery() {
	}

	public UpdateQuery(DBMetaData dbmd) {
		super(dbmd.getDialect(), dbmd);
	}

	/**
	 * Konstruktor tridy UpdateQuery.
	 *
	 * @param dialect - dialect
	 * @param dbmd - dbmd
	 */
	public UpdateQuery(Dialect dialect, DBMetaData dbmd) {
		super(dialect, dbmd);
	}
	
	/**
	 * Konstruktor vytvori nonou instanci objektu DeleteQuery s vlastnostmi
	 * objektu zadaneho jako parametr konstruktoru.
	 *  
	 * @param orderBy
	 */
	public UpdateQuery(UpdateQuery query) throws Exception {
		this();
		UpdateQuery clone = (UpdateQuery)query.clone();
		
		setId(clone.getId());
		setName(clone.getName());
		setDialect(clone.getDialect());
		setDBMetaData(clone.dbmd());
		setAliasMap(clone.getAliasMap());

		this.tables = clone.getTables();
		this.joins = clone.getJoins();
		this.parameters = clone.getParameters();
		setWhere(clone.getWhere());
	}

	
	// ----- -----------------------------------------------------------------

	/* (non-Javadoc)
	 * @see cz.acies.uniql.sql.Query#translate()
	 */
	public String translate() throws Exception {
		String tableName = "";
		Dialect dialect = this.getDialect();

		StringBuffer sb = new StringBuffer("");
		sb.append("UPDATE ");
		if (this.getTables() != null) {
			for (Iterator<UniQLExpr> i = getTables().iterator(); i.hasNext();) {
				UniQLExpr table = i.next();
				tableName = ((UniQLFrom)table).getName();
				// v MSSQL nesm� m�t alias pokud m� dotaz JOIN a nebo  
				if (dialect.isUpdateJoinWithFrom()  &&  this.getJoins() != null && !this.getJoins().isEmpty()) {
					if (((UniQLFrom)table).getAlias() != null) {
						table = new UniQLAlias(((UniQLFrom)table).getAlias());
					} 
				}
				table.setUniQLProperties(this);
				sb.append(table.translate());
				if (i.hasNext()) sb.append(", ");
			}
		}
		if (!dialect.isUpdateJoinWithFrom()  &&  this.getJoins() != null) {
			for (Iterator<UniQLExpr> i = getJoins().iterator(); i.hasNext();) {
				UniQLExpr join = i.next();
				join.setUniQLProperties(this);
				((UniQLJoin) join).setAliasMap(getAliasMap());
				sb.append("\n" + join.translate());
			}
		}
		if (this.getParameters() != null) {
			sb.append(" SET ");
			int j = 0;
			for (Iterator<HashMap<String, Object>> i = getParameters().iterator(); i.hasNext();) {				
				TableMetaData tbmd = this.dbmd().getTable(tableName);
				HashMap<String, Object> map = i.next();
				UniQLExpr expr = (UniQLExpr)map.get("expr");
				if (expr != null) {
					expr.setUniQLProperties(this);
					sb.append(expr.translate()+" = ");
					
					Object obj = map.get("value");
					if (obj instanceof UniQLExpr) {
						((UniQLExpr)obj).setUniQLProperties(this);
						sb.append(((UniQLExpr)obj).translate());
					} else if (obj instanceof String) {
						String value = obj.toString();
						try {							
							String colname = expr.getName();
							//if (colname != null) colname = colname/*.toUpperCase()*/; 
							FieldMetaData fmd = (FieldMetaData)tbmd.getFieldByName(colname);
							int type = fmd.getSqlType();
							int length = fmd.getSize();
							sb.append(convertValueByType(dialect, type, value, length));
						} catch (Exception exc) {
							System.out.println("[ERROR] UpdateQuery.translate().column.value = "+value);
							exc.printStackTrace();							
						}
					} else if (obj == null) {
						String value = null;
						String colname = null;
						try {							
							colname = expr.getName();
							if (colname != null) colname = colname.toUpperCase(); 
							FieldMetaData fmd = (FieldMetaData)tbmd.getFieldByName(colname);
							boolean nullable = fmd.isNullable();
							String defVal = fmd.getDefaultValue();
							int type = fmd.getSqlType();
							int length = fmd.getSize();							
							
							if (nullable) {
								sb.append("NULL");							
							} else {
								if (defVal == null) {
									value = getBlankValue(type).toString();
								} else {
									value = defVal;
								}
								sb.append(convertValueByType(dialect, type, value, length));
							}
							//sb.append(convertValueByType(dialect, type, value, length));
						} catch (Exception exc) {
							System.out.println("[ERROR] UpdateQuery.translate(153).column = "+colname+", value = "+value);
							exc.printStackTrace();							
						}						
					}
					if (i.hasNext()) sb.append(", \n");
				}
				j++;
			}
		}
		if (dialect.isUpdateJoinWithFrom()  &&  this.getJoins() != null && !this.getJoins().isEmpty()) {
			UniQLExpr from = (UniQLExpr)this.getTables().get(0);
			if (from != null) {
				from.setUniQLProperties(this);
				sb.append("\nFROM "+from.translate()+" ");
				for (Iterator<UniQLExpr> i = getJoins().iterator(); i.hasNext();) {
					UniQLExpr join = (UniQLExpr) i.next();
					join.setUniQLProperties(this);
					((UniQLJoin) join).setAliasMap(getAliasMap());
					sb.append("\n" + join.translate());
				}
			}
		}
		if (this.getWhere() != null && !this.getWhere().isEmpty()) {
			this.where.setUniQLProperties(this);
			sb.append(" \n"+this.where.translate());
		}
		return sb.toString();
	}

	/**
	 * Metoda vraci nulovou hodnotu pro dany typ 
	 * @param type
	 * @return
	 */
	private Object getBlankValue(int type) {
		Object value = "";
		switch (type) {
			case Types.CHAR:
			case Types.VARCHAR:
			case Types.LONGVARCHAR:
			case Types.BLOB: {
				value = "";
				break;
			}
			case Types.BIT:
			case Types.TINYINT:
			case Types.SMALLINT:
			case Types.NUMERIC:
			case Types.INTEGER:
			case Types.BIGINT: {
				value = new Integer(0);
				break;
			}
			case Types.DECIMAL:
			case Types.REAL:
			case Types.FLOAT:
			case Types.DOUBLE: {
				value = new Double(0.00);
				break;
			}
			case Types.DATE: {
				value = java.sql.Date.valueOf("1900-01-01");
				break;
			}
			case Types.TIMESTAMP: {
				value = Timestamp.valueOf("1900-01-01 00:00:01");
				break;
			}
		}
		return value;
	}

	/**
	 * Metoda vynuluje v�echny elementy v objektu 
	 * 
	 * @return Vrac� objekt typu UpdateQuery
	 */	
	public UpdateQuery clear() {
		if (tables != null) tables.clear();	tables = null;
		if (joins != null) joins.clear();	joins = null;
		if (parameters != null) parameters.clear();	parameters = null;
		if (where != null) where.clear(); where = null;
		
		return this;
	}
	
	// ----- ------------------ ---------------------------------------------

	/**
	 * Metoda pro p�id�n� parametru pro z�pis dat.
	 * 
	 * @param expr - objekt typu UniQLExpr ud�v�j�c� jm�no sloupce do kter�ho bude zapisov�no
	 * @param value - objekt typu UniQLExpr obsahuj�c� zapisovanou hodnotu
	 * @return Vrac� objekt typu UpdateQuery
	 */
	public UpdateQuery addParam(UniQLExpr expr, UniQLExpr value) {
		HashMap<String, Object> map = new HashMap<String, Object>();
		expr.setUniQLProperties(this);
		map.put("expr", expr);
		value.setUniQLProperties(this);
		map.put("value", value);
		this.getParameters().add(map);
		return this;
	}

	/**
	 * Metoda pro p�id�n� parametru pro z�pis dat.
	 * 
	 * @param expr - objekt typu UniQLExpr ud�v�j�c� jm�no sloupce do kter�ho bude zapisov�no
	 * @param value - znakov� �et�zec obsahuj�c� zapisovanou hodnotu
	 * @return Vrac� objekt typu UpdateQuery
	 */
	public UpdateQuery addParam(UniQLExpr expr, String value) {
		HashMap<String, Object> map = new HashMap<String, Object>();
		expr.setUniQLProperties(this);
		map.put("expr", expr);
		map.put("value", value);
		this.getParameters().add(map);
		return this;
	}

	/**
	 * Metoda pro p�id�n� parametru pro z�pis dat.
	 * 
	 * @param expr - znakov� �et�zec ud�v�j�c� jm�no sloupce do kter�ho bude zapisov�no
	 * @param value - znakov� �et�zec obsahuj�c� zapisovanou hodnotu
	 * @return Vrac� objekt typu UpdateQuery
	 */	
	public UpdateQuery addParam(String expr, String value) {
		HashMap<String, Object> map = new HashMap<String, Object>();
		UniQLColumn column = new UniQLColumn(expr);
		column.setUniQLProperties(this);
		map.put("expr", column);
		map.put("value", value);
		this.getParameters().add(map);
		return this;
	}

	/**
	 * Metoda pro p�id�n� parametru pro z�pis dat.
	 * 
	 * @param expr - znakov� �et�zec ud�v�j�c� jm�no sloupce do kter�ho bude zapisov�no
	 * @param value - parametr typu UniQLExpr obsahuj�c� zapisovanou hodnotu (nap�. SQLFunc, UniQLColumn atd.)
	 * @return Vrac� objekt typu UpdateQuery
	 */	
	public UpdateQuery addParam(String expr, UniQLExpr value) {
		HashMap<String, Object> map = new HashMap<String, Object>();
		UniQLColumn column = new UniQLColumn(expr);
		column.setUniQLProperties(this);
		map.put("expr", column);
		value.setUniQLProperties(this);
		map.put("value", value);
		this.getParameters().add(map);
		return this;
	}
	
	/**
	 * Metoda pro smaz�n� parametru z�pisu dat.
	 * 
	 * @param expr - znakov� �et�zec ud�v�j�c� jm�no sloupce v takov�m form�tu v 
	 * jak�m byl do UpdateQuery p��d�m metodou addParam  
	 * @return Vrac� objekt typu UpdateQuery
	 */	
	public UpdateQuery removeParam(String expr) {
		String tableName = null;
		String columnName = null;
		int pos = expr.indexOf(".");
		if (pos > -1) {
			tableName = expr.substring(0, pos);
			columnName = expr.substring(pos+1);
		} else {
			columnName = expr;
		}
		int i = 0;
		Iterator<HashMap<String, Object>> iter = this.getParameters().iterator();
		while (iter.hasNext()) {
			HashMap<String, Object> map = iter.next();
			UniQLColumn column = (UniQLColumn)map.get("expr");
			if (tableName == null) {
				if (expr.equals(column.getName())) break;
			} else {
				if (tableName.equals(column.getTableName()) && columnName.equals(column.getName())) break;
			}
			i++;
		}
		//System.out.println("UpdateQuery.removeParam().i = "+i);
		if (i < this.getParameters().size() && i > -1) this.getParameters().remove(i);
		return this;
	}

	/**
	 * Metoda p�id� do dotazu klauzuli Where
	 * @param 	where
	 * @return	UpdateQuery
	 */
	public UpdateQuery setWhere(UniQLWhere where) {
		//System.out.println("UpdateQuery.setWhere()");
		this.where = where;
		return this;
	}

	/**
	 * Metoda p�id� do dotazu do klauzule UniQLWhere obsah parametru UniQLWhere
	 * POZOR na na pr�ci ve smy�ce, kde where (jeho instance) je ji� p�id�n do
	 * UpdateQuery v prvn�m pr�chodu.
	 *  
	 * @param 	where
	 * @return	UpdateQuery
	 */
	public UpdateQuery addWhere(UniQLWhere where) {
		if (this.where == null || this.where.isEmpty()) {
			this.where = new UniQLWhere();
			this.where.setUniQLProperties(this);
		}
		this.where.addCriterions(where.getCriterions());
		return this;
	}
	
	/**
	 * @param cf
	 * @return
	 */
	public UpdateQuery addWhereCondition(UniQLCondition cf) {
		if (this.where == null) {
			this.where = new UniQLWhere();
			this.where.setUniQLProperties(this);
		}
		this.where.addCondition(cf);
		return this;
	}

	/**
	 * To sam� co metoda addWhereCondition(UniQLCondition cf).
	 * 
	 * @param cf - cf
	 * @return vraci UpdateQuery
	 */
	public UpdateQuery add(UniQLCondition cf) {
		return addWhereCondition(cf);
	}

	/**
	 * Metoda p�id� do Where klauzuli dotazu podm�nku a oper�tor AND nebo OR, 
	 * kter�m bude podm�nka navazovat na dal�� podm�nku. Pokud dal�� podm�nka 
	 * neexistuje, je oper�tor ignorov�n.
	 * 
	 * @param cf 	Parametr typu UniQLCondition 
	 * @param op	�et�zcov� promenn� mus� m�t hodnotu AND nebo OR
	 * @return		UpdateQuery
	 */	
	public UpdateQuery addWhereCondition(UniQLCondition cf, String op) {
		if (this.where == null) {
			this.where = new UniQLWhere();
			this.where.setUniQLProperties(this);
		}
		this.where.addCondition(cf, op);
		return this;
	}
	
	/**
	 * To sm� co metoda addWhereCondition(UniQLCondition cf, String op).
	 * 
	 * @param cf - cf
	 * @param op - op
	 * @return vraci UpdateQuery
	 */
	public UpdateQuery add(UniQLCondition cf, String op) {
		return addWhereCondition(cf, op);
	}

	/**
	 * Metoda prida do UniQLWhere klauzuli dotazu podminku a operator AND nebo OR, 
	 * ktery bude vlozen mezi predchozi a pridavanou podminku. Pokud predchozi 
	 * podminka neexistuje, je operator ignorovan.
	 * 
	 * @param cf Parametr typu UniQLCondition
	 * @param op retezcova promenna musi mit hodnotu AND nebo OR
	 * 
	 * @return objekt UpdateQuery
	 */
	public UpdateQuery addWhereCondition(String op, UniQLCondition cf) {
		if (this.where == null) {
			this.where = new UniQLWhere();
			this.where.setUniQLProperties(this);
		}
		this.where.setLastConditionOperand(op);
		this.where.addCondition(cf);
		return this;
	}

	/**
	 * Metoda schodn�a s addWhereCondition(op, cf)
	 * 
	 * @param op
	 * @param cf
	 * @return objrct UpdateQuery
	 */
	public UpdateQuery add(String op, UniQLCondition cf) {
		return addWhereCondition(op, cf);
	}

	/**
	 * Metoda prida do UniQLWhere klauzuli dotazu podminku a operator1 AND nebo OR, 
	 * ktery bude vlozen mezi predchozi a pridavanou podminku a na ni nastaven operator2. 
	 * Pokud predchozi podminka neexistuje, je operator1 ignorovan.
	 * 
	 * @param op1 retezcova promenna musi mit hodnotu AND nebo OR
	 * @param cf Parametr typu UniQLCondition
	 * @param op2 retezcova promenna musi mit hodnotu AND nebo OR
	 * 
	 * @return objekt UpdateQuery
	 */
	public UpdateQuery addWhereCondition(String op1, UniQLCondition cf, String op2) {
		if (this.where == null) {
			this.where = new UniQLWhere();			
			this.where.setUniQLProperties(this);
		}
		where.setLastConditionOperand(op1);
		where.add(cf, op2);
		return this;
	}

	/**
	 * Metoda schodna s addWhereCondition(op1, cf, op2)
	 * 
	 * @param op
	 * @param cf
	 * @return object UpdateQuery
	 */
	public UpdateQuery add(String op1, UniQLCondition cf, String op2) {
		return addWhereCondition(op1, cf, op2);
	}

	/**
	 * P�id� ke stavajicim podm�nk�m WHERE klausule nov�, z obsa�en� v parametru criterions.
	 * 
	 * @param c
	 * @return
	 */
	public UpdateQuery addWhereCriterion(UniQLCriterion c) {
		if (this.where == null) {
			this.where = new UniQLWhere();
			this.where.setUniQLProperties(this);
		}
		this.where.addCriterions(c);
		return this;
	}

	/**
	 * Metoda schodn� s addWhereCriterion(c) 
	 * @param c
	 * @return
	 */
	public UpdateQuery add(UniQLCriterion c) {
		return addWhereCriterion(c);
	}

	/**
	 * Metoda nejprve nastav� LastConditionOperator na parametr operator ve st�vj�c�m 
	 * seznamu podm�nek a pak p�id� dal�� podm�nky odsa�en� s objectu UniQLCrierion.
	 * 
	 * @param op
	 * @param c
	 * @return
	 */
	public UpdateQuery addWhereCriterion(String op, UniQLCriterion c) {
		if (this.where == null) {
			this.where = new UniQLWhere();
			this.where.setUniQLProperties(this);
		}
		this.where.addCriterions(op, c);
		return this;
	}

	/**
	 * Metoda schodn� s addWhereCriterion(op, c) 
	 * @param c
	 * @return
	 */
	public UpdateQuery add(String op, UniQLCriterion c) {
		return addWhereCriterion(op, c);
	}


	/**
	 * Navraci object where.
	 * 
	 * @return - vraci hodnotu where
	 */
	public UniQLWhere getWhere() {
		return this.where;
	}

	/**
	 * Metoda vyma�e (vynuluje) odsah where. 
	 * @return UpdateQuery
	 */
	public UpdateQuery removeWhere() {
		this.where = null;
		return this;
	}

	/**
	 * Vraci true, pokud je aktivni objekt where.
	 * 
	 * @return - vraci true, pokud je aktivni objekt where.
	 */
	public boolean hasWhere() {
		if (this.where == null) return false;
		if (this.where.isEmpty()) return false;
		return true;
	}

	//-----  ------------------------------------------------------------------

	public UpdateQuery addFrom(String tableName) {
		UniQLFrom from = new UniQLFrom(tableName);
		return this.addFrom(from);
	}

	public UpdateQuery setFrom(String tableName) {
		UniQLFrom from = new UniQLFrom(tableName);
		return this.setFrom(from);
	}

	public UpdateQuery addFrom(String tableName, String alias) {
		UniQLFrom from = new UniQLFrom(tableName, alias);
		return this.addFrom(from);
	}

	public UpdateQuery setFrom(String tableName, String alias) {
		UniQLFrom from = new UniQLFrom(tableName, alias);
		return this.setFrom(from);
	}

	public UpdateQuery addFrom(UniQLFrom from) {
		try {
			HashMap<String, Object> aliasMap = this.getAliasMap();
			HashMap<String, Object> fromIndex = (HashMap<String, Object>)getFromAliasMap(); //aliasMap.get("fromIndex"); 
			if (fromIndex == null) {
				fromIndex = new HashMap<String, Object>();
				aliasMap.put("fromIndex", fromIndex);
			}
			from.setUniQLProperties(this);
			String alias = from.getAlias();
			String table = from.getName();
			if (alias != null && alias.length() > 0) {
				if (table != null && table.length() > 0) {
					aliasMap.put(table.toLowerCase(), table.toLowerCase());
					if (!aliasMap.containsKey("mainFrom")) {
						aliasMap.put("mainFrom", alias.toLowerCase());
						fromIndex.put(alias.toLowerCase(), from);
					}
				}
			} else {
				if (table != null && table.length() > 0) {
					aliasMap.put(table.toLowerCase(), table.toLowerCase());
					if (!aliasMap.containsKey("mainFrom")) {
						aliasMap.put("mainFrom", table.toLowerCase());
						fromIndex.put(table.toLowerCase(), from);
					}
				}
			}
			getTables().add(from);
		} catch (Exception exc) {
			exc.printStackTrace();
		}
		return this;
	}

	@SuppressWarnings("unchecked")
	public UpdateQuery setFrom(UniQLFrom from) {
		from.setUniQLProperties(this);
		String alias = from.getAlias();
		String table = from.getName();
		getTables().clear();
		HashMap<String, Object> aliasMap = this.getAliasMap();
		HashMap<String, Object> fromIndex = (HashMap<String, Object>)aliasMap.get("fromIndex"); 
		if (alias != null && alias.length() > 0) {
			// System.out.println("UpdateQuery.addFrom(1010)");
			if (table != null && table.length() > 0) {
				aliasMap.put(alias.toLowerCase(), table.toLowerCase());
				if (!aliasMap.containsKey("mainFrom")) {
					aliasMap.put("mainFrom", alias.toLowerCase());
					fromIndex.put(alias.toLowerCase(), from);
				}
			}
		} else {
			// System.out.println("UpdateQuery.addFrom(1011)");
			if (table != null && table.length() > 0) {
				aliasMap.put(table.toLowerCase(), table.toLowerCase());
				if (!aliasMap.containsKey("mainFrom")) {
					aliasMap.put("mainFrom", table.toLowerCase());
					fromIndex.put(alias.toLowerCase(), from);
				}
			}
		}
		getTables().add(from);
		return this;
	}

	// ----- -----------------------------------------------------------------
	
	/**
	 * Prida objekt UniQLJoin do kontejneru joins.
	 * 
	 * @param join - objekt typu UniQLJoin.
	 * 
	 * @return objekt UpdateQuery
	 */
	public UpdateQuery addJoin(UniQLJoin join) {
		if (join != null && join instanceof UniQLJoin) {
			join.setUniQLProperties(this);
			UniQLExpr from = join.getFrom();
			if (from instanceof UniQLFrom) {
				String alias = ((UniQLFrom)from).getAlias();
				if (alias != null && alias.length() > 0) {
					String table = from.getName();
					if (table != null && table.length() > 0) {
						this.getAliasMap().put(alias.toLowerCase(), table.toLowerCase());
					}
				}
			}
			this.getJoins().add(join);
		}
		return this;
	}

	/**
	 * Prida objekt UniQLJoin do kontejneru joins.
	 * 
	 * @param join - objekt typu UniQLJoin.
	 * 
	 * @return objekt UpdateQuery
	 */
	public UpdateQuery setJoin(UniQLJoin join) {
		if (join != null && join instanceof UniQLJoin) {
			join.setUniQLProperties(this);
			UniQLExpr from = join.getFrom();
			if (from instanceof UniQLFrom) {
				String alias = ((UniQLFrom)from).getAlias();
				if (alias != null && alias.length() > 0) {
					String table = from.getName();
					if (table != null && table.length() > 0) {
						this.getAliasMap().put(alias.toLowerCase(), table.toLowerCase());
					}
				}
			}
			this.getJoins().clear();
			this.getJoins().add(join);
		}
		return this;
	}

	/**
	 * Vraci kontejner joins.
	 * 
	 * @return - vraci hodnotu joins
	 */
	public ArrayList<UniQLExpr> getJoins() {
		if (joins == null) {
			joins = new ArrayList<UniQLExpr>();
		}
		return joins;
	}

	// ----- -----------------------------------------------------------------

	public ArrayList<UniQLExpr> getTables() {
		if (tables == null) {
			tables = new ArrayList<UniQLExpr>();
		}
		return tables;
	}

	public ArrayList<HashMap<String, Object>> getParameters() {
		if (parameters == null) {
			parameters = new ArrayList<HashMap<String, Object>>();
		}
		return parameters;
	}

	// ----- -----------------------------------------------------------------
	
	public String toSQLString() throws Exception {
		return this.translate();
	}

	
}
